#!/usr/bin/python
# -*- coding: utf-8 -*-
import json
import datetime
from flask import render_template, request, redirect, Blueprint
import fileutils
import xlrd
from werkzeug.datastructures import CombinedMultiDict
from forms import UploadForm
import dbOperate

db1 = dbOperate.dbOperate('10.37.133.21', 'spider', 'spider', 'spider')
duty = Blueprint('duty', __name__)
fileutils.file_read()
today = datetime.date.today()


@duty.route('/get_duty', methods=['GET'])
def get_duty():
    # sql1 = 'select * from duty where weekday = ' + '"' +str(today) +'"'
    sql1 = "select * from duty where weekday = date_format(NOW(), '%Y-%m-%d')"
    see = db1.exe_query(sql1)
    hostname = []
    value = []
    jsonData = {}
    for data in see:
        hostname.append(str(data[0]))
        value.append(data[1])

    jsonData['hostname'] = hostname
    jsonData['value'] = value
    j = json.dumps(jsonData, ensure_ascii=False)
    # print ('duty--------' + j)
    return (j)


@duty.route('/dutylist/')
def dutylist():
    userlist = fileutils.file_read().items()
    # print('userlist:%s' % userlist)
    return render_template('dutylist.html', userlist=userlist)


@duty.route('/dutyupdate/')
def dutyupdate():
    username = request.args.get('username')
    dit = fileutils.file_read()
    password = dit.get(username)
    user = [username, password]
    # print('update:%s' % user)
    return render_template('dutyupdate.html', user=user)


@duty.route('/dutyupdateaction/', methods=['POST'])
def dutyupdateaction():
    params = request.args if request.method == 'GET' else request.form
    username = params.get('username')
    password = params.get('password')
    fileutils.file_dict[username] = password
    fileutils.file_write()
    return redirect('/dutylist/')


@duty.route('/dutyadd/')
def dutyadd():
    day = str(today)
    print(day)
    # print('update:%s' % day)
    return render_template('dutyadd.html', day=day)


@duty.route('/dutyaddaction/', methods=['POST'])
def dutyaddaction():
    params = request.args if request.method == 'GET' else request.form
    username = params.get('username')
    password = params.get('password')
    if username in fileutils.file_dict:
        return redirect('/dutylist/')
    else:
        # fileutils.file_dict[username] = password
        fileutils.file_ins(username, password)
        return redirect('/dutylist/')


@duty.route('/dutydelete/')
def dutydelete():
    username = request.args.get('username')
    fileutils.file_del(username)
    return redirect('/dutylist/')


@duty.route('/dutyimport/', methods=['GET', 'POST'])
def dutyimport():
    sql = "insert into duty (weekday,person) values (%s,%s)"
    if request.method == 'GET':
        return render_template('dutyimport.html')
    else:
        form = UploadForm(CombinedMultiDict([request.form, request.files]))
        if form.validate():
            image_file = form.image_file.data
            f = image_file.read()
            try:
                data = xlrd.open_workbook(file_contents=f)
            except:
                return "导入人员信息失败，请确认文件类型是否加密"
            sheet_1 = data.sheet_by_index(0)  # 根据sheet页的排序选取sheet
            row_number = sheet_1.nrows  # 获取有数据的最大行数
            print  (row_number)
            if row_number < 61:
                for i in range(1, row_number):
                    try:
                        day = sheet_1.cell(i, 0).value
                        weekday = xlrd.xldate_as_datetime(day, 0).strftime('%Y-%m-%d')
                        person = sheet_1.cell(i, 1).value
                    except:
                        return "请按照模板上传xls文件"
                    Values = (weekday, person)
                    print (Values)
                    try:
                        db1.exe_modify(sql, Values)
                    except:
                        return "sql执行失败，请确认是否有重复数据"
            else:
                return "数据量过大，请分段导入"
            return "导入人员信息成功"
        else:
            return "文件上传失败，文件类型不合法或者为空"
